﻿using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Text;
using Besture.Common;
using Besture.Model;

namespace Besture.Dal
{
    public partial class HotelsDal : BaseDataAccess
    {

        /// <summary>
        /// 获取详细信息
        /// </summary>
        /// <param name="id">主键值</param>
        /// <returns></returns>
        public HotelsModel GetModel(int id)
        {
            try
            {
                StringBuilder selectSql = new StringBuilder();
                selectSql.Append("SELECT * FROM TBHotels");
                selectSql.Append(" WHERE ");
                selectSql.Append(" ID=@ID ");
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                base.SetParameter(ldcDBCommand, "@ID", DbType.Int32, id);
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                if (dt.Rows.Count > 0)
                    return ModelConvertHelper<HotelsModel>.ConvertToModelList(dt)[0];
                else return null;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        /// <summary>
        /// 获取全部数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetDataTable()
        {
            try
            {
                StringBuilder selectSql = new StringBuilder();
                selectSql.Append(string.Format(@"
                SELECT th.*,thc.CategoryName AS AreaName,thc1.CategoryName AS HotelsStarsName,thc2.CategoryName AS CharacteristicIDName,thc3.CategoryName AS BrandIDName 
                FROM dbo.TBHotels th
                INNER JOIN dbo.TBHotelsCategory thc ON thc.ID=th.Area AND thc.IsDeleted=0 AND th.IsDeleted=0 AND thc.CategoryType={0}
                INNER JOIN dbo.TBHotelsCategory thc1 ON thc1.ID=th.HotelsStars AND thc1.IsDeleted=0 AND th.IsDeleted=0 AND thc1.CategoryType={1}
                INNER JOIN dbo.TBHotelsCategory thc2 ON thc2.ID=th.CharacteristicID AND thc2.IsDeleted=0 AND th.IsDeleted=0 AND thc2.CategoryType={2}
                INNER JOIN dbo.TBHotelsCategory thc3 ON thc3.ID=th.BrandID AND thc3.IsDeleted=0 AND th.IsDeleted=0 AND thc3.CategoryType={3}
                ORDER BY th.CreateTime DESC ", Constants.HOTEL_CATEGORY_LOCATION
                                             , Constants.HOTEL_CATEGORY_STARS
                                             , Constants.HOTEL_CATEGORY_CHARACTERISTIC
                                             , Constants.HOTEL_CATEGORY_BRAND));
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                return dt;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(HotelsModel model)
        {
            var identityId = -1;
            try
            {


                string sqlString = string.Format(@"
                INSERT INTO TBHotels
                (
                        HotelsName
                        ,HotelsImage
                        ,Tel
                        ,Address
                        ,Longitude
                        ,Latitude
                        ,Detail
                        ,NetworkFacilities
                        ,ParkingLot
                        ,HotelFacilities
                        ,Area
                        ,HotelsStars
                        ,CharacteristicID
                        ,BrandID
                        ,FacilitiesID
                        ,Score
                        ,SpecialOffer
                        ,LatestTime
                        ,Introduction
                        ,RecommendationList
                        ,RecommendationPay
                        ,RecommendationHome
                        ,TopHotels
                        ,SeoTitle
                        ,SeoKeywords
                        ,SeoDescription
                        ,SortID
                        ,IsDeleted
                        ,Created
                        ,CreateTime
                        ,Updated
                        ,UpdateTime
                        ,WidthImageUrl
                        ,FastTime
                )
                VALUES
                (
                        @HotelsName
                       ,@HotelsImage
                       ,@Tel
                       ,@Address
                       ,@Longitude
                       ,@Latitude
                       ,@Detail
                       ,@NetworkFacilities
                       ,@ParkingLot
                       ,@HotelFacilities
                       ,@Area
                       ,@HotelsStars
                       ,@CharacteristicID
                       ,@BrandID
                       ,@FacilitiesID
                       ,@Score
                       ,@SpecialOffer
                       ,@LatestTime
                       ,@Introduction
                       ,@RecommendationList
                       ,@RecommendationPay
                       ,@RecommendationHome
                       ,@TopHotels
                       ,@SeoTitle
                       ,@SeoKeywords
                       ,@SeoDescription
                       ,@SortID
                       ,@IsDeleted
                       ,@Created
                       ,@CreateTime
                       ,@Updated
                       ,@UpdateTime
                       ,@WidthImageUrl
                       ,@FastTime
                )SELECT @@IDENTITY");

                DbCommand ldcDBCommand = base.GetCommand(sqlString);

                //参数设定
                SetParameter(ldcDBCommand, "@HotelsName", DbType.String, model.HotelsName);
                SetParameter(ldcDBCommand, "@HotelsImage", DbType.String, model.HotelsImage);
                SetParameter(ldcDBCommand, "@Tel", DbType.String, model.Tel);
                SetParameter(ldcDBCommand, "@Address", DbType.String, model.Address);
                SetParameter(ldcDBCommand, "@Longitude", DbType.String, model.Longitude);
                SetParameter(ldcDBCommand, "@Latitude", DbType.String, model.Latitude);
                SetParameter(ldcDBCommand, "@Detail", DbType.String, model.Detail);
                SetParameter(ldcDBCommand, "@NetworkFacilities", DbType.String, model.NetworkFacilities);
                SetParameter(ldcDBCommand, "@ParkingLot", DbType.String, model.ParkingLot);
                SetParameter(ldcDBCommand, "@HotelFacilities", DbType.String, model.HotelFacilities);
                SetParameter(ldcDBCommand, "@Area", DbType.Int32, model.Area);
                SetParameter(ldcDBCommand, "@HotelsStars", DbType.Int32, model.HotelsStars);
                SetParameter(ldcDBCommand, "@CharacteristicID", DbType.Int32, model.CharacteristicID);
                SetParameter(ldcDBCommand, "@BrandID", DbType.Int32, model.BrandID);
                SetParameter(ldcDBCommand, "@FacilitiesID", DbType.String, model.FacilitiesID);
                SetParameter(ldcDBCommand, "@Score", DbType.Int32, model.Score);
                SetParameter(ldcDBCommand, "@SpecialOffer", DbType.String, model.SpecialOffer);
                SetParameter(ldcDBCommand, "@LatestTime", DbType.String, model.LatestTime);
                SetParameter(ldcDBCommand, "@Introduction", DbType.String, model.Introduction);
                SetParameter(ldcDBCommand, "@RecommendationList", DbType.Int32, model.RecommendationList);
                SetParameter(ldcDBCommand, "@RecommendationPay", DbType.Int32, model.RecommendationPay);
                SetParameter(ldcDBCommand, "@RecommendationHome", DbType.Int32, model.RecommendationHome);
                SetParameter(ldcDBCommand, "@TopHotels", DbType.Int32, model.TopHotels);
                SetParameter(ldcDBCommand, "@SeoTitle", DbType.String, model.SeoTitle);
                SetParameter(ldcDBCommand, "@SeoKeywords", DbType.String, model.SeoKeywords);
                SetParameter(ldcDBCommand, "@SeoDescription", DbType.String, model.SeoDescription);
                SetParameter(ldcDBCommand, "@SortID", DbType.Int32, model.SortID);
                SetParameter(ldcDBCommand, "@IsDeleted", DbType.Int32, model.IsDeleted);
                SetParameter(ldcDBCommand, "@Created", DbType.Int32, model.Created);
                SetParameter(ldcDBCommand, "@CreateTime", DbType.DateTime, model.CreateTime);
                SetParameter(ldcDBCommand, "@Updated", DbType.Int32, model.Updated);
                SetParameter(ldcDBCommand, "@UpdateTime", DbType.DateTime, model.UpdateTime);
                SetParameter(ldcDBCommand, "@WidthImageUrl", DbType.String, model.WidthImageUrl);
                SetParameter(ldcDBCommand, "@FastTime", DbType.String, model.FastTime);

                identityId = base.ExecuteScalarInsert(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return identityId;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Update(HotelsModel model)
        {
            int iTmp = -1;
            try
            {

                string sqlString = string.Format(@"
                UPDATE TBHotels
                SET
                    HotelsName = @HotelsName
                    ,HotelsImage = @HotelsImage
                    ,Tel = @Tel
                    ,Address = @Address
                    ,Longitude = @Longitude
                    ,Latitude = @Latitude
                    ,Detail = @Detail
                    ,NetworkFacilities = @NetworkFacilities
                    ,ParkingLot = @ParkingLot
                    ,HotelFacilities = @HotelFacilities
                    ,Area = @Area
                    ,HotelsStars = @HotelsStars
                    ,CharacteristicID = @CharacteristicID
                    ,BrandID = @BrandID
                    ,FacilitiesID = @FacilitiesID
                    ,Score = @Score
                    ,SpecialOffer = @SpecialOffer
                    ,LatestTime = @LatestTime
                    ,Introduction = @Introduction
                    ,RecommendationList = @RecommendationList
                    ,RecommendationPay = @RecommendationPay
                    ,RecommendationHome = @RecommendationHome
                    ,TopHotels = @TopHotels
                    ,SeoTitle = @SeoTitle
                    ,SeoKeywords = @SeoKeywords
                    ,SeoDescription = @SeoDescription
                    ,SortID = @SortID
                    ,IsDeleted = @IsDeleted
                    ,Created = @Created
                    ,Updated = @Updated
                    ,UpdateTime = @UpdateTime
                    ,FastTime = @FastTime
                WHERE
                    ID = @ID
            ");

                DbCommand ldcDBCommand = base.GetCommand(sqlString);
                //绑定SQL参数
                SetParameter(ldcDBCommand, "@ID", DbType.Int32, model.ID);
                SetParameter(ldcDBCommand, "@HotelsName", DbType.String, model.HotelsName);
                SetParameter(ldcDBCommand, "@HotelsImage", DbType.String, model.HotelsImage);
                SetParameter(ldcDBCommand, "@Tel", DbType.String, model.Tel);
                SetParameter(ldcDBCommand, "@Address", DbType.String, model.Address);
                SetParameter(ldcDBCommand, "@Longitude", DbType.String, model.Longitude);
                SetParameter(ldcDBCommand, "@Latitude", DbType.String, model.Latitude);
                SetParameter(ldcDBCommand, "@Detail", DbType.String, model.Detail);
                SetParameter(ldcDBCommand, "@NetworkFacilities", DbType.String, model.NetworkFacilities);
                SetParameter(ldcDBCommand, "@ParkingLot", DbType.String, model.ParkingLot);
                SetParameter(ldcDBCommand, "@HotelFacilities", DbType.String, model.HotelFacilities);
                SetParameter(ldcDBCommand, "@Area", DbType.Int32, model.Area);
                SetParameter(ldcDBCommand, "@HotelsStars", DbType.Int32, model.HotelsStars);
                SetParameter(ldcDBCommand, "@CharacteristicID", DbType.Int32, model.CharacteristicID);
                SetParameter(ldcDBCommand, "@BrandID", DbType.Int32, model.BrandID);
                SetParameter(ldcDBCommand, "@FacilitiesID", DbType.String, model.FacilitiesID);
                SetParameter(ldcDBCommand, "@Score", DbType.Int32, model.Score);
                SetParameter(ldcDBCommand, "@SpecialOffer", DbType.String, model.SpecialOffer);
                SetParameter(ldcDBCommand, "@LatestTime", DbType.String, model.LatestTime);
                SetParameter(ldcDBCommand, "@Introduction", DbType.String, model.Introduction);
                SetParameter(ldcDBCommand, "@RecommendationList", DbType.Int32, model.RecommendationList);
                SetParameter(ldcDBCommand, "@RecommendationPay", DbType.Int32, model.RecommendationPay);
                SetParameter(ldcDBCommand, "@RecommendationHome", DbType.Int32, model.RecommendationHome);
                SetParameter(ldcDBCommand, "@TopHotels", DbType.Int32, model.TopHotels);
                SetParameter(ldcDBCommand, "@SeoTitle", DbType.String, model.SeoTitle);
                SetParameter(ldcDBCommand, "@SeoKeywords", DbType.String, model.SeoKeywords);
                SetParameter(ldcDBCommand, "@SeoDescription", DbType.String, model.SeoDescription);
                SetParameter(ldcDBCommand, "@SortID", DbType.Int32, model.SortID);
                SetParameter(ldcDBCommand, "@IsDeleted", DbType.Int32, model.IsDeleted);
                SetParameter(ldcDBCommand, "@Created", DbType.Int32, model.Created);
                SetParameter(ldcDBCommand, "@Updated", DbType.Int32, model.Updated);
                SetParameter(ldcDBCommand, "@UpdateTime", DbType.DateTime, model.UpdateTime);
                SetParameter(ldcDBCommand, "@FastTime", DbType.String, model.FastTime);
                

                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return iTmp;
        }

        /// <summary>
        /// 单个删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Delete(int id)
        {
            int iTmp = -1;
            try
            {
                StringBuilder updateSql = new StringBuilder();
                updateSql.Append("DELETE FROM TBHotels");
                updateSql.Append(" WHERE ");
                updateSql.Append(" ID=@ID ");
                DbCommand ldcDBCommand = base.GetCommand(updateSql.ToString());
                base.SetParameter(ldcDBCommand, "@ID", DbType.Int32, id);
                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return iTmp;
        }

        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="idList"></param>
        /// <returns></returns>
        public int DeleteList(string ids)
        {
            int iTmp = -1;
            try
            {
                StringBuilder updateSql = new StringBuilder();
                updateSql.Append("exec('DELETE FROM TBHotels");
                updateSql.Append(" WHERE ");
                updateSql.Append(" ID in ('+@ID+') ')");
                DbCommand ldcDBCommand = base.GetCommand(updateSql.ToString());
                base.SetParameter(ldcDBCommand, "@ID", DbType.String, ids);
                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return iTmp;
        }

        /// <summary>
        /// 获取首页推荐酒店
        /// </summary>
        /// <returns></returns>
        public IList<Index_Hotel_Model> GetHomeHotels()
        {
            try
            {
                StringBuilder selectSql = new StringBuilder();
                selectSql.Append("SELECT TOP(4) h.ID AS HotelID,h.HotelsName,h.HotelsImage,h.Introduction,h.WidthImageUrl,h.TopHotels,h.RecommendationHome,h.UpdateTime,c.CategoryName AS Area,1 AS [Type] FROM TBHotels AS h LEFT JOIN TBHotelsCategory AS c ON c.ID=h.Area WHERE h.TopHotels=1 AND h.IsDeleted=0 AND c.IsDeleted=0 AND c.CategoryType=1 UNION ALL SELECT TOP(5) h.ID AS HotelID,h.HotelsName,h.HotelsImage,h.Introduction,h.WidthImageUrl,h.TopHotels,h.RecommendationHome,h.UpdateTime,c.CategoryName AS Area1,2 AS [Type] FROM TBHotels AS h LEFT JOIN TBHotelsCategory AS c ON c.ID=h.Area WHERE h.RecommendationHome=1 AND h.IsDeleted=0 AND c.IsDeleted=0 AND c.CategoryType=1 ORDER BY h.UpdateTime DESC");
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                if (dt.Rows.Count > 0)
                    return ModelConvertHelper<Index_Hotel_Model>.ConvertToModelList(dt);
                else return null;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        /// <summary>
        /// 查询酒店列表
        /// </summary>
        /// <returns></returns>
        public DataSet GetHotels(string filter, string pageindex, int userID, int usertype, out string total)
        {
            try
            {
                string[] arr = filter.Split('_');
                DbCommand ldcDBCommand = base.GetStoredProcCommand("up_QueryHotelList");
                //绑定SQL参数
                SetParameter(ldcDBCommand, "@USER_ID", DbType.Int32, userID);
                SetParameter(ldcDBCommand, "@USER_TYPE", DbType.Int32, usertype);
                SetParameter(ldcDBCommand, "@HOTEL_NAME", DbType.String, arr[0]);
                SetParameter(ldcDBCommand, "@BEGIN_TIME", DbType.String, arr[1] == "" ? DateTime.Now.ToString("yyyy-MM-dd") : Convert.ToDateTime(arr[1]).ToString("yyyy-MM-dd"));
                SetParameter(ldcDBCommand, "@END_TIME", DbType.String, arr[2] == "" ? DateTime.Now.AddDays(1).ToString("yyyy-MM-dd") : Convert.ToDateTime(arr[2]).ToString("yyyy-MM-dd"));
                SetParameter(ldcDBCommand, "@AREA", DbType.Int32, arr[3]);
                SetParameter(ldcDBCommand, "@BEGIN_PRICE", DbType.Decimal, arr[4]);
                SetParameter(ldcDBCommand, "@END_PRICE", DbType.Decimal, arr[5]);
                SetParameter(ldcDBCommand, "@HOTEL_STAR", DbType.Int32, arr[6]);
                SetParameter(ldcDBCommand, "@CHARACTERISTIC", DbType.Int32, arr[7]);
                SetParameter(ldcDBCommand, "@BRAND", DbType.String, arr[8].Trim(','));
                SetParameter(ldcDBCommand, "@FACILITIES", DbType.String, arr[9].Trim(','));
                SetParameter(ldcDBCommand, "@ORDER_NAME", DbType.String, arr[10]);
                SetParameter(ldcDBCommand, "@ORDER_TYPE", DbType.String, arr[11]);
                SetParameter(ldcDBCommand, "@PAGE_INDEX", DbType.Int32, pageindex);
                SetParameter(ldcDBCommand, "@PAGE_SIZE", DbType.Int32, 3);
                SetParameter(ldcDBCommand, "@TOTAL", DbType.Int32, 0, ParameterDirection.Output);

                DataSet ds = base.GetDataSet(ldcDBCommand);
                total = ldcDBCommand.Parameters["@TOTAL"].Value.ToString();
                return ds;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        /// <summary>
        /// 获取推荐酒店列表
        /// </summary>
        /// <returns></returns>
        public DataSet GetHotelsRecommend(int userID)
        {
            try
            {
                DbCommand ldcDBCommand = base.GetStoredProcCommand("up_GetHotelsRecommend");
                SetParameter(ldcDBCommand, "@UserID", DbType.Int32, userID);
                DataSet ds = base.GetDataSet(ldcDBCommand);
                return ds;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        /// <summary>
        /// 获取推荐酒店列表
        /// 会员支付页面显示
        /// </summary>
        /// <returns></returns>
        public DataSet GetHotelsRecommendPay(int userID)
        {
            try
            {
                DbCommand ldcDBCommand = base.GetStoredProcCommand("up_GetHotelsRecommendPay");
                SetParameter(ldcDBCommand, "@UserID", DbType.Int32, userID);
                DataSet ds = base.GetDataSet(ldcDBCommand);
                return ds;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        /// <summary>
        /// 查询酒店详情
        /// </summary>
        /// <returns></returns>
        public DataSet GetHotelsByID(int hotelid)
        {
            try
            {
                DbCommand ldcDBCommand = base.GetStoredProcCommand("up_QueryHotelByID");
                SetParameter(ldcDBCommand, "@HOTEL_ID", DbType.Int32, hotelid);
                DataSet ds = base.GetDataSet(ldcDBCommand);
                return ds;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        /// <summary>
        /// 根据酒店ID查询房态
        /// </summary>
        /// <returns></returns>
        public DataSet GetRoomByHotelID(int userType, int hotelid, string begintime, string endtime)
        {
            try
            {
                DbCommand ldcDBCommand = base.GetStoredProcCommand("up_QueryRoomByHotelID");
                SetParameter(ldcDBCommand, "@USER_TYPE", DbType.Int32, userType);
                SetParameter(ldcDBCommand, "@HOTEL_ID", DbType.Int32, hotelid);
                SetParameter(ldcDBCommand, "@BEGIN_TIME", DbType.String, begintime == "" ? DateTime.Now.ToString("yyyy-MM-dd") : Convert.ToDateTime(begintime).ToString("yyyy-MM-dd"));
                SetParameter(ldcDBCommand, "@END_TIME", DbType.String, endtime == "" ? DateTime.Now.AddDays(1).ToString("yyyy-MM-dd") : Convert.ToDateTime(endtime).ToString("yyyy-MM-dd"));
                DataSet ds = base.GetDataSet(ldcDBCommand);
                return ds;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }
    }
}